/**
 * 
 */
package com.ermax.aircond.common.dao;

import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.DoubleType;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import com.ermax.aircond.common.domain.ReadMachineData;
import com.ermax.aircond.common.domain.ReadMachineDataAudit;
import com.ermax.aircond.util.dao.AbstractHibernateDao;

/**
 * @author Eric, Maxwell
 * 
 */
@Repository("readMachineDataAuditDao")
public class ReadMachineDataAuditDao extends AbstractHibernateDao<ReadMachineData> {

	private static final Logger logger = Logger.getLogger(ReadMachineDataAuditDao.class);

	public ReadMachineDataAuditDao() {
		setClazz(ReadMachineData.class);
	}

	@SuppressWarnings("unchecked")
	public List<ReadMachineDataAudit> findMachineDateByNid(int nid, Date fromDate, Date toDate) {
		Criteria criteria = this.getCurrentSession().createCriteria(ReadMachineDataAudit.class);
		criteria.add(Restrictions.eq("nid", nid));
		
		if(fromDate != null){
			criteria.add(Restrictions.ge("updateDate", fromDate));
		}
		
		if(toDate != null){
			criteria.add(Restrictions.le("updateDate", toDate));
		}	
		
		criteria.addOrder(Order.asc("updateDate"));
		
		List<ReadMachineDataAudit> readMachineDatas = criteria.list();
		return readMachineDatas;
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> findMachineDataByNidGroupByDay(int nid, Date fromDate, Date toDate) {
		Criteria criteria = this.getCurrentSession().createCriteria(ReadMachineDataAudit.class);
		criteria.add(Restrictions.eq("nid", nid));
		
		if(fromDate != null){
			criteria.add(Restrictions.ge("updateDate", fromDate));
		}
		
		if(toDate != null){
			criteria.add(Restrictions.le("updateDate", toDate));
		}	
		
		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.sqlGroupProjection(
				"year(updateDate) as yearly, month(updateDate) as monthly, day(updateDate) as daily, 0 as hourly, round(avg(defaulttemp),0) as avgdefaulttemp, round(avg(roomtemp),0) as avgroomtemp, round(avg(mintemp),0) as avgmintemp",	//sql 
				"year(updateDate), month(updateDate), day(updateDate)",	//groupBy 
				new String[]{"yearly", "monthly", "daily", "hourly", "avgdefaulttemp","avgroomtemp", "avgmintemp"},	//columnAliases 
				new Type[] {IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE}));
		
		criteria.setProjection(projList);
		
		List<Object[]> readMachineDatas = criteria.list();
		return readMachineDatas;
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> findMachineDataByNidGroupByHour(int nid, Date fromDate, Date toDate) {
		Criteria criteria = this.getCurrentSession().createCriteria(ReadMachineDataAudit.class);
		criteria.add(Restrictions.eq("nid", nid));
		
		if(fromDate != null){
			criteria.add(Restrictions.ge("updateDate", fromDate));
		}
		
		if(toDate != null){
			criteria.add(Restrictions.le("updateDate", toDate));
		}	
		
		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.sqlGroupProjection(
				"year(updateDate) as yearly, month(updateDate) as monthly, day(updateDate) as daily, DATEPART(HH,updateDate) as hourly, round(avg(defaulttemp),0) as avgdefaulttemp, round(avg(roomtemp),0) as avgroomtemp, round(avg(mintemp),0) as avgmintemp",	//sql 
				"year(updateDate), month(updateDate), day(updateDate), DATEPART(HH,updateDate)",	//groupBy 
				new String[]{"yearly", "monthly", "daily", "hourly", "avgdefaulttemp","avgroomtemp", "avgmintemp"},	//columnAliases
				new Type[] {IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE,}));
		
		criteria.setProjection(projList);
		
		List<Object[]> readMachineDatas = criteria.list();
		return readMachineDatas;
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> findMachineDataByNidGroupBySixHour(int nid, Date fromDate, Date toDate) {
		Criteria criteria = this.getCurrentSession().createCriteria(ReadMachineDataAudit.class);
		criteria.add(Restrictions.eq("nid", nid));
		
		if(fromDate != null){
			criteria.add(Restrictions.ge("updateDate", fromDate));
		}
		
		if(toDate != null){
			criteria.add(Restrictions.le("updateDate", toDate));
		}	
		
		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.sqlGroupProjection(
				"year(updateDate) as yearly, month(updateDate) as monthly, day(updateDate) as daily, round(DATEPART(HH,updateDate)/6,0)*6 as hourly, round(avg(defaulttemp),0) as avgdefaulttemp, round(avg(roomtemp),0) as avgroomtemp, round(avg(mintemp),0) as avgmintemp",	//sql 
				"year(updateDate), month(updateDate), day(updateDate), round(DATEPART(HH,updateDate)/6,0)*6 ",	//groupBy 
				new String[]{"yearly", "monthly", "daily", "hourly", "avgdefaulttemp","avgroomtemp", "avgmintemp"},	//columnAliases
				new Type[] {IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, IntegerType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE, DoubleType.INSTANCE,}));
		
		criteria.setProjection(projList);
		
		List<Object[]> readMachineDatas = criteria.list();
		return readMachineDatas;
	}

}
